SELECT  C.COMPANY||SUBSTR( '&[-C--L]Company',1,0)                "COMPANY",
            C.PROJECT_GROUP  "PROJECT GROUP"  ,
            B.PROJECT_ID     "PROJECT ID"    ,
            ifsapp.ACCOUNT_API.Get_Logical_Account_Type_Db( b.company, b.account) accnt_type,
            B.ACCOUNT  "ACCOUNT"         ,
            C.PROJECT_STATUS     ,
           to_char(MAX( C.READY_DATE) ,'YYYY-MM-DD')    "COMPLETION DATE",
            D.PROJECT_TYPE    "PROJECT TYPE"     ,
            C.PROJECT_LEADER     "PROJECT LEADER"   ,
            SUM( amount_balance)   "total_amount" ,
            SUM( DECODE( SIGN( TO_NUMBER(B.ACCOUNTING_YEAR||DECODE(SIGN(10-B.ACCOUNTING_PERIOD),1,'0'||B.ACCOUNTING_PERIOD,B.ACCOUNTING_PERIOD)) - TO_NUMBER('&[M-S--]Until_year'||DECODE(SIGN(10-'&[M-S--]From_period'),1,'0'||  '&[M-S--]From_period','&[M-S--]From_period'))),-1,0,1,b.amount_balance,0,b.amount_balance))  +  DECODE('&[M-S--]until_period',1,0,0)  amount_balance,

            SUM( DECODE( SIGN( TO_NUMBER(B.ACCOUNTING_YEAR||DECODE(SIGN(10-B.ACCOUNTING_PERIOD),1,'0'||B.ACCOUNTING_PERIOD,B.ACCOUNTING_PERIOD)) - TO_NUMBER('&[M-S--]Until_year'||'01')),-1,0,1,b.amount_balance,0,b.amount_balance)) until_amount_balance,
            SUM( DECODE(B.BUDGET_VERSION, '&Budget_version',B.BUDGET_AMOUNT,0)) total_budget_amount,             
            SUM( DECODE( SIGN( TO_NUMBER(B.ACCOUNTING_YEAR||DECODE(SIGN(10-B.ACCOUNTING_PERIOD),1,'0'||B.ACCOUNTING_PERIOD,B.ACCOUNTING_PERIOD)) - TO_NUMBER( '&[M-S--]Until_year'||DECODE(SIGN(10-'&[M-S--]From_period'),1,'0'||'&[M-S--]From_period','&[M-S--]From_period'))),-1,0,1,DECODE(B.BUDGET_VERSION, '&Budget_version',B.BUDGET_AMOUNT, 0),0,DECODE(B.BUDGET_VERSION, '&Budget_version',B.BUDGET_AMOUNT, 0))) budget_amount,
            SUM( DECODE( SIGN( TO_NUMBER(B.ACCOUNTING_YEAR||DECODE(SIGN(10-B.ACCOUNTING_PERIOD),1,'0'||B.ACCOUNTING_PERIOD,B.ACCOUNTING_PERIOD)) - TO_NUMBER('&[M-S--]Until_year'||'01')),-1,0,0,DECODE(B.BUDGET_VERSION, '&Budget_version', B.BUDGET_AMOUNT),1,DECODE(B.BUDGET_VERSION, '&Budget_version',B.BUDGET_AMOUNT))) until_budget_amount
      FROM  ifsapp.PROJECT_BALANCE_BUDGET B
          inner join   ifsapp.ACCOUNTING_PROJECT   C    on B.PROJECT_ID= C.PROJECT_ID       and B.COMPANY  = C.COMPANY
           inner join   ifsapp.ACCOUNTING_PROJECT_LEDGER D    on   C.PROJECT_ID= D.PROJECT_ID  and    C.COMPANY  = D.COMPANY
   
   WHERE 
    (   C.COMPANY   LIKE   NVL( '&[-C--L]Company' ,'%')  OR C.Company     in (SELECT REGEXP_SUBSTR(d__.str, '[^;]+', 1, LEVEL) AS val_            FROM (SELECT '&[-C--L]Company' AS str FROM DUAL) d__     CONNECT BY LEVEL <= REGEXP_COUNT(d__.str, ';') + 1)   )
   AND      D.ledger_id              IN ('*', '00') 
AND   B.ACTIVATE_CODE          = 'COST/REV'
 AND D.project_type != 'P' AND Account_API.Get_Logical_Account_Type_Db(b.company , b.account)  IN ('R','C')
AND   (TO_NUMBER(B.ACCOUNTING_YEAR||DECODE(SIGN(10-B.ACCOUNTING_PERIOD),1,'0'||B.ACCOUNTING_PERIOD,B.ACCOUNTING_PERIOD)) <= TO_NUMBER('&[M-S--]Until_year'||DECODE(SIGN(10-'&[M-S--]until_period'),1,'0'||'&[M-S--]until_period','&[M-S--]until_period')))
AND   ('&Project_status'  = '%'            OR   C.PROJECT_STATUS LIKE  NVL( '&Project_status','%' )         OR C.PROJECT_STATUS IS NULL) 


       GROUP BY C.COMPANY,C.PROJECT_GROUP, B.PROJECT_ID,
               ifsapp.ACCOUNT_API.Get_Logical_Account_Type_Db( b.company, b.account),
               B.ACCOUNT, D.PROJECT_TYPE, C.PROJECT_LEADER, C.PROJECT_STATUS
      ORDER BY  C.COMPANY,C.PROJECT_GROUP, B.PROJECT_ID, accnt_type DESC, B.ACCOUNT ASC